package test.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ReadExcel {

    public static void read(String filePath) throws Exception{
        read(filePath,0,0);
    }


    public static void read(String filePath,int startRow) throws Exception{
        read(filePath,startRow,0);
    }

    public static void read(String filePath,int startRow,int sheetNum) throws Exception{

        File file = new File(filePath);

        String fileName = file.getName();
        boolean is2003 = true;    //判断是否是excel2007格式
        if (fileName.endsWith("xlsx")) {
            is2003 = false;
        }
        InputStream ins = null;
        try {
            ins = new FileInputStream(file);
            Workbook wb = null;
            //根据文件格式(2003或者2007)来初始化
            if (is2003) {
                wb = new HSSFWorkbook(ins);
            } else {
                wb = new XSSFWorkbook(ins);
            }
            Sheet sheet = wb.getSheetAt(sheetNum);

            for (int i = startRow ; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);

                featureBesCategory(row,filePath,startRow,sheetNum);

            }
        } catch (IOException e) {
            throw new Exception(e.getMessage());
        } finally {
            ins.close();
        }
    }

    /**
     * 百度bes更换行业2020-03-11
     * @param row
     * @param filePath
     * @param startRow
     * @param sheetNum
     * @throws Exception
     */
    private static void featureBesCategory(Row row,String filePath,int startRow,int sheetNum) throws Exception{

        /**
         * select a.catg_id,count(*) from ad_category a where a.catgserial = 'BES' group by a.catg_id ;
         *
         * select * from ad_category a where a.catgserial = 'BES' and a.catg_id = '8301' ;
         *
         * select a.catgid,count(*) from partner_category a where a.catgserial = 'BES' group by a.catgid ;
         */

        String oldCatgId = getCellValue(row.getCell(0));
        if (isHaveChinese(oldCatgId)) {
            throw new Exception("Excel中存在汉字或者为空的无效列");
        }

        String newCatgId = getCellValue(row.getCell(2));

        if(sheetNum == 0){
            System.out.println("update ad_category_copy set catg_id='" + newCatgId +"' where catg_id='" + oldCatgId + "' and catgserial = 'BES';");
        }
        else{
            System.out.println("update partner_category_copy set catgid='" + newCatgId +"' where catgid='" + oldCatgId + "' and catgserial = 'BES';");
        }
    }

    private static String getCellValue(Cell cell) {
        String cellValue = "";
        // 以下是判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    private static boolean isHaveChinese(String str) {
        boolean flag = true;
        if (StringUtils.isEmpty(str)) {
            return flag;
        }
        int count = 0;
        String regEx = "[\\u4e00-\\u9fa5]";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(str);

        while (m.find()) {
            for (int i = 0; i <= m.groupCount(); i++) {
                count = count + 1;
            }
        }
        if (count == 0) {
            flag = false;
        }
        return flag;
    }


    public static void main(String[] args) {

        try {
            ReadExcel.read("/Users/zhangrui/Desktop/aaa.xlsx",1,1);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
